""" Thsi program will fetch the link form commnets and store then in  mysql database """


import MySQLdb
import extract_link
import tldextract
import url_redi
import httplib
import re

"""=========Server=== Denzil====waste==
data_db = "chromium"
comment_table = "issues"  ## Table having description of bug reports not comment
data_user = "sangeeta"
data_pass = "sangeeta@goldbug"
data_port = 3307

store_db = "apsec2013"
store_table = "description_link"
store_user = "Sangeeta"
store_pass ="sangeeta@123456"
store_port=3306
"""

data_db = "apsec2013"
comment_table = "issues"
data_user = "root"
data_pass = "123"
data_port =3306

store_db = "apsec2013"
store_table = "description_link"
store_user = "root"
store_pass ="123"
store_port=3306
#======"""

data_db= MySQLdb.connect(host="localhost", user=data_user, passwd=data_pass,db=data_db, port=data_port)
cursor_data = data_db.cursor()

store_db = MySQLdb.connect(host="127.0.0.1", user=store_user, passwd=store_pass,db=store_db, port=store_port)
cursor_store = store_db.cursor()


String  = "select issue_id, content from " +comment_table  +" where issue_id>=230742" # + " limit 0,20000"
cursor_data.execute(String);
rows = cursor_data.fetchall()
for row in rows:
    issue_id = row[0]
    #comment_id =row[1]
    links = extract_link.get_links(row[1])
    #links =extract_link.filter_links(links)
    if  links:
        print "empty", links
        link_id =1
        for link in links:
            
            link = link.split("\'")[0]
            link = link.split(">")[0]
            link = link.split(")")[0]
            link = link.split("(")[0]
            link = link.split("]")[0]
            link = link.split("[")[0]
            link = link.split("\"")[0]
            link = link.split("%")[0]
            link = link.split(",")[0]
            
            original_link =  "original_link"
            
            link   = link.split("&quot;")[0]
            link = link.replace("&lt;","")
            link = link.replace("&gt;","")
            link = link.replace("///","//")
            link = link.replace("\\"," ")
            link = re.sub('https?://./','http://', link)
            link = re.sub('[.]+','.',link)
            
            link = re.sub('https?://[-\.\*\?\[\]\(\)@&%!~`^$#{}/xX:]+[0-9]*/?$','http://',link)
            link =re.sub('https?://[0-9\.:]+/?$','http://',link)
            link =re.sub('https?://localhost/?$','http://',link)
            link =re.sub('https?://hostname/?$','http://',link)
            
            if link[len(link)-1]=='.' or link[len(link)-1]==';':
                link = link[0:len(link)-1]
            print "link=", link
            #link = extract_link.clean_link(link)
            if len(link) > 10:
                try:
                    link = url_redi.get_redirected_url(link)
                except httplib.InvalidURL, e:
                    continue
                link = link.replace("'","")   
                link = link[0:2000]
                tld_val = tldextract.extract(link)
                tld_subdomain = tld_val[0]
                tld_domain = tld_val[1]
                tld_tld= tld_val[2]
              #  complete_domain=extract_link.get_complete_domain(link)
               # main_domain  = extract_link.get_main_domain(link)
                print issue_id#,comment_id, link
                insert_str = "insert into "+ store_table +" values("+(str)(issue_id)+","+\
                "'nothing'"+","+(str)(link_id)+",'" +link+"','"+\
                tld_subdomain+"','"+tld_domain+"','"+tld_tld+"','"+original_link+"')"
                print insert_str
                cursor_store.execute(insert_str)
                link_id=link_id+1
                print link_id
                store_db.commit()   
                 
del_str1 = "delete from "+ store_table+" where tld_domain  like '%*%' AND tld_subdomain like ''"
#or tld_domain like '%localhost%' or tld_domain like '%hostname%'"+\
#" or tld_domain like '%127.0.0.1%' or tld_domain like '%127.0.0.1%' or tld_subdomain like '%subdomain%'"         
cursor_store.execute(del_str1)
print "del str1=", del_str1
del_str2 = "delete from " + store_table+\
" where tld_subdomain like '' and tld_domain like '' and tld_tld like ''"
print "del_str2=",del_str2
cursor_store.execute(del_str2)
del_str3 = "delete from "+ store_table+" where link like '%example.com%'"
print "del_str3=", del_str3
cursor_store.execute(del_str3)
del_str4 = "delete from "+store_table+" where tld_subdomain like '%x%' and tld_domain like 'x%'"
print "del_str4 = ", del_str4
cursor_store.execute(del_str4)
del_str5= "delete  from "+ store_table+" where tld_domain like '%foobar%'"
print "del_str5=",del_str5
cursor_store.execute(del_str5)
del_str6 = " delete from "+ store_table +"  where tld_domain like 'foo'"
cursor_store.execute(del_str6)
print "del_str6=", del_str6

del_str7 ="delete from "+ store_table+" where tld_subdomain like 'foo' and tld_domain like 'bar'"
cursor_store.execute(del_str7)
print "del_tsr7", del_str7

del_str8 = "delete from "+ store_table +"  where tld_domain like '%blah%'  or tld_domain like 'whatever'"
cursor_store.execute(del_str8)
print "del_str8=",del_str8

"""
"select * from description_link where tld_domain  like '%*%' or tld_domain like '%localhost%' or tld_domain like '%hostname%' or tld_domain like '%127.0.0.1%' or tld_domain like '%127.0.0.1%' or tld_subdomain like '%subdomain%'"
"""

store_db.commit()                
              
                #if links is not None:
    #    print issue_id, comment_id,links
    #else:  
    #    print comment_id,  "I am none" 
        



